package com.iplant.aps.service.utils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.iplant.aps.service.mesenum.DBEnumType;
import com.iplant.aps.service.po.Pagination;

public class ShrisJdbcTemplate extends NamedParameterJdbcTemplate {

	public ShrisJdbcTemplate(JdbcOperations classicJdbcTemplate, DBEnumType wDBType) {

		super(classicJdbcTemplate);
		DBType = wDBType;
		// TODO Auto-generated constructor stub
	}

	public ShrisJdbcTemplate(DataSource dataSource, DBEnumType wDBType) {
		super(dataSource);
		DBType = wDBType;
		// TODO Auto-generated constructor stub
	}

	public DBEnumType DBType = DBEnumType.Default;

	public List<Map<String, Object>> queryForList(String wSQL, Map<String, Object> wParamMap, Pagination wPagination) {

		if (wPagination == null || wPagination.PageSize <= 0)
			return super.queryForList(wSQL, wParamMap);

		wSQL.trim();

		if (wSQL.endsWith(";")) {
			wSQL = wSQL.substring(0, wSQL.length() - 1);
		}
		this.PrevQueryForList(wSQL, wParamMap, wPagination);

		if (wPagination.TotalCount <= 0)
			return new ArrayList<Map<String, Object>>();

		wSQL = ShrisJdbcTemplate.SqlPageSql(wSQL, DBType, wPagination.Sort, wPagination.SortType, wPagination.PageSize,
				wPagination.PageIndex).toString();

		return super.queryForList(wSQL, wParamMap);
	}

	private void PrevQueryForList(String wSQL, Map<String, Object> wParamMap, Pagination wPagination) {

		String wSqlTemp = "SELECT COUNT(0) as ItemCount FROM (" + wSQL + ") CountTable;";
		Map<String, Object> wResult = super.queryForMap(wSqlTemp, wParamMap);

		if (wResult != null && wResult.containsKey("ItemCount")) {

			wPagination.TotalCount = StringUtils.parseInt(wResult.get("ItemCount"));
		}

	}

	public static StringBuilder SqlPageSql(String strSql, DBEnumType wDBType, String sort, boolean isAsc, int pageSize,
			int pageIndex) {
		StringBuilder sb = null;
		switch (wDBType) {
		case Default:

			break;
		case MySQL:
			sb = SqlPageMySql(strSql, sort, isAsc, pageSize, pageIndex);
			break;
		case SQLServer:
			sb = SqlPageSqlServer(strSql, sort, isAsc, pageSize, pageIndex);
			break;
		case Oracle:
			break;
		case Access:
			break;
		default:
			break;
		}
		return sb;
	}

	public static StringBuilder SqlPageSql(String strSql, DBEnumType wDBType, String sort, String sortType,
			int pageSize, int pageIndex) {
		StringBuilder sb = null;

		if (StringUtils.isEmpty(sortType))
			sortType = "desc";

		boolean isAsc = sortType.equalsIgnoreCase("ASC");

		switch (wDBType) {
		case Default:

			break;
		case MySQL:
			sb = SqlPageMySql(strSql, sort, isAsc, pageSize, pageIndex);
			break;
		case SQLServer:
			sb = SqlPageSqlServer(strSql, sort, isAsc, pageSize, pageIndex);
			break;
		case Oracle:
			break;
		case Access:
			break;
		default:
			break;
		}
		return sb;
	}

	private static StringBuilder SqlPageSqlServer(String strSql, String sort, boolean isAsc, int pageSize,
			int pageIndex) {
		StringBuilder sb = new StringBuilder();
		if (pageIndex <= 0) {
			pageIndex = 1;
		}
		int num = (pageIndex - 1) * pageSize;
		int num1 = (pageIndex) * pageSize;
		String OrderBy = "";

		if (StringUtils.isNotEmpty(sort)) {
			if (sort.toUpperCase().indexOf("ASC") + sort.toUpperCase().indexOf("DESC") > 0) {
				OrderBy = " ORDER BY " + sort;
			} else {
				OrderBy = " ORDER BY " + sort + " " + (isAsc ? "ASC" : "DESC");
			}
		} else {
			OrderBy = "ORDERE BY (SELECT 0)";
		}
		sb.append("SELECT * FROM (SELECT ROW_NUMBER() Over (" + OrderBy + ")");
		sb.append(" AS ROWNUM, * From (" + strSql + ") t ) AS N WHERE ROWNUM > " + num + " AND ROWNUM <= " + num1 + "");
		return sb;
	}

	private static StringBuilder SqlPageMySql(String strSql, String sort, boolean isAsc, int pageSize, int pageIndex) {
		StringBuilder sb = new StringBuilder();
		if (pageIndex <= 0) {
			pageIndex = 1;
		}

		String OrderBy = "";

		if (StringUtils.isNotEmpty(sort)) {
			if (sort.toUpperCase().indexOf("ASC") + sort.toUpperCase().indexOf("DESC") > 0) {
				OrderBy = " ORDER BY " + sort;
			} else {
				OrderBy = " ORDER BY " + sort + " " + (isAsc ? "ASC" : "DESC");
			}
		}

		sb.append(strSql + OrderBy + " limit " + ((pageIndex - 1) * pageSize) + "," + pageSize);
		return sb;
	}

}
